package com.propertypoint.persitence.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.propertypoint.exception.DatabaseException;
import com.propertypoint.presentation.model.KeyValue;
import com.propertypoint.presentation.model.Log;
import com.propertypoint.util.DBUtil;

 
public class AuditDAOImpl implements AuditDAO {
	private static final String INSERT_LOG = "INSERT INTO SEARCH_LOG"
			+ "(IP_ADDRESS,TYPE,RENT_SALE,BEDROOMS,COUNT,LOCATION,MIN_PRICE,MAX_PRICE) VALUES"
			+ "(?,?,?,?,?,?,?,?)";
	
	private static String SELECT_HIT_COUNT_BY_LOCATION_HDB="select count(id) as hit_count,location   from search_log a where a.type='HDB' group by  location";

	@Override
	public void logSearchResult(Log log) throws Exception {
		Connection con = DBUtil.getDBUtil().getConnection();
		PreparedStatement preparedStatement = con.prepareStatement(INSERT_LOG,
				Statement.RETURN_GENERATED_KEYS);

		preparedStatement.setString(1, log.getIp());// IP_ADDRESS
		preparedStatement.setString(2, log.getType());// LAST_NAME
		preparedStatement.setString(3, log.getRentSale());// PHONE_NUMBER
		preparedStatement.setString(4, log.getBedrooms());// EMAIL
		preparedStatement.setString(5, log.getResultCount());// ADDRESS
		preparedStatement.setString(6, log.getLocation());// LOCATION
		preparedStatement.setString(7, log.getMinPrice());// MIN_PRICE
		preparedStatement.setString(8, log.getMaxPrice());// MAX_PRICE

		try {
			// execute insert SQL stetement
			preparedStatement.executeUpdate();
		} catch (Exception e) {
			preparedStatement.close();
			con.close();
			throw new DatabaseException(e.toString());
		} finally {
			preparedStatement.close();
			con.close();
		}

	}

	@Override
	public List<Log> getSearchLog() {
		// TODO Auto-generated method stub
		return null;
	}
	
	public List<KeyValue> getSearchHitsHDBLocation() throws Exception{
		Connection con = DBUtil.getDBUtil().getConnection();
		PreparedStatement preparedStatement = con.prepareStatement(SELECT_HIT_COUNT_BY_LOCATION_HDB);
		List<KeyValue> keyValues=new ArrayList<KeyValue>();
		
		
		try {
			ResultSet rs=preparedStatement.executeQuery();
			while(rs.next()){
				int count=rs.getInt("hit_count");
				String location=rs.getString("location");
				KeyValue obj=new KeyValue(location,String.valueOf(count));
				keyValues.add(obj);
			}
		} catch (Exception e) {
			preparedStatement.close();
			con.close();
			throw new DatabaseException(e.toString());
		} finally {
			preparedStatement.close();
			con.close();
		}
		return keyValues;
	}

}
